package com.hbnu.common.dao.impl;

import com.hbnu.common.dao.CheckInItemDao;
import com.hbnu.common.entity.CheckInItem;
import com.hbnu.common.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * {@link CheckInItemDao} 接口的实现类，负责处理与打卡项相关的数据库操作。
 * 依赖 {@link DBUtil} 工具类获取数据库连接，并使用 try-with-resources 确保资源自动关闭。
 */

public class CheckInItemDaoImpl implements CheckInItemDao {

    /**
     * {@inheritDoc}
     */
    @Override
    public void addItem(String itemName, String username) throws SQLException {
        String sql = "insert into check_in_item (item_name, username) values(?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, itemName);
            pstmt.setString(2, username);

            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.addItem: pstmt.executeUpdate() < 1");
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public CheckInItem getItemById(int itemId) throws SQLException {
        String sql = "select i.item_id, i.item_name, i.username, i.continuous_days, i.total_days, " +
                "r.record_id is not null as is_today_checked " +
                "from check_in_item as i " +
                "left join check_in_record as r " +
                "on i.item_id = r.item_id and r.check_in_date = curdate() " +
                "where i.item_id = ?";

        try (Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, itemId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new CheckInItem(
                            rs.getInt("item_id"),
                            rs.getString("item_name"),
                            rs.getString("username"),
                            rs.getInt("total_days"),
                            rs.getInt("continuous_days"),
                            rs.getBoolean("is_today_checked")
                    );
                }
            }
        }

        return null;
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public List<CheckInItem> getItemsOfUser(String username) throws SQLException {
        List<CheckInItem> items = new ArrayList<>();
        String sql = "select i.item_id, i.item_name, i.username, i.continuous_days, i.total_days, " +
                "r.record_id is not null as is_today_checked " +
                "from check_in_item as i " +
                "left join check_in_record as r " +
                "on i.item_id = r.item_id and r.check_in_date = curdate() " +
                "where i.username = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, username);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    items.add(new CheckInItem(
                            rs.getInt("item_id"),
                            rs.getString("item_name"),
                            rs.getString("username"),
                            rs.getInt("total_days"),
                            rs.getInt("continuous_days"),
                            rs.getBoolean("is_today_checked")
                    ));
                }
            }
        }

        return items;
    }

    /**
     * {@inheritDoc}
     * <p>
     * <strong>实现细节：</strong>
     * 本方法通过手动管理数据库连接和事务来保证操作的原子性。
     * 它会依次调用以下私有辅助方法来完成整个流程：
     * <ol>
     * <li>{@link #insertCheckInRecord(Connection, int)}</li>
     * <li>{@link #updateTotalDays(Connection, int)}</li>
     * <li>{@link #calculateContinuousDays(Connection, int)}</li>
     * <li>{@link #updateContinuousDays(Connection, int, int)}</li>
     * </ol>
     */
    @Override
    public void checkInToday(int itemId) throws SQLException {
        Connection conn = null;

        try {
            conn = DBUtil.getConnection();
            // 开启事务（确保插入记录和更新天数要么都成功，要么都失败）
            conn.setAutoCommit(false);

            insertCheckInRecord(conn, itemId);
            updateTotalDays(conn, itemId);

            int newContinuousDays = calculateContinuousDays(conn, itemId);
            updateContinuousDays(conn, itemId, newContinuousDays);

            // 提交事务
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException rollbackEx) {
                    throw new SQLException("CheckInItemDao.checkInToday: conn.rollback() 失败");
                }
            }
            throw e;
        } finally {
            DBUtil.close(null, null, conn);
        }
    }

    /**
     * 向打卡记录表插入今日打卡记录（内部辅助方法，需在事务中调用）
     *
     * @param conn    数据库连接（共享事务）
     * @param itemId  打卡项ID
     * @throws SQLException 当SQL执行失败或插入未生效时抛出
     */
    private void insertCheckInRecord(Connection conn, int itemId) throws SQLException {
        String sql = "insert into check_in_record (item_id, check_in_date) values(?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, itemId);
            pstmt.setDate(2, java.sql.Date.valueOf(LocalDate.now()));

            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.insertCheckInRecord: pstmt.executeUpdate() < 1");
            }
        }
    }

    /**
     * 更新打卡项的总打卡天数（+1，内部辅助方法，需在事务中调用）
     *
     * @param conn   数据库连接（共享事务）
     * @param itemId 打卡项ID
     * @throws SQLException 当SQL执行失败或未找到对应打卡项时抛出
     */
    private void updateTotalDays(Connection conn, int itemId) throws SQLException {
        String sql = "update check_in_item set total_days = total_days + 1 where item_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, itemId);
            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.updateTotalDays: pstmt.executeUpdate() < 1");
            }
        }
    }

    /**
     * 计算打卡项的最新连续打卡天数（内部辅助方法，需在事务中调用）：
     * - 若昨日已打卡，连续天数=当前连续天数+1
     * - 若昨日未打卡，连续天数=1
     *
     * @param conn   数据库连接（共享事务）
     * @param itemId 打卡项ID
     * @return 计算后的最新连续打卡天数
     * @throws SQLException 当SQL执行失败或未找到对应打卡项时抛出
     */
    private int calculateContinuousDays(Connection conn, int itemId) throws SQLException {
        LocalDate yesterday = LocalDate.now().minusDays(1);
        String sqlCheckYesterday = "select record_id from check_in_record where item_id = ? and check_in_date = ?";
        boolean hasYesterdayCheckIn = false;

        try (PreparedStatement pstmt = conn.prepareStatement(sqlCheckYesterday)) {
            pstmt.setInt(1, itemId);
            pstmt.setDate(2, java.sql.Date.valueOf(yesterday));

            try (ResultSet yesterdayRs = pstmt.executeQuery()) {
                hasYesterdayCheckIn = yesterdayRs.next();
            }
        }

        if (hasYesterdayCheckIn) {
            String sqlGetCurrent = "select continuous_days from check_in_item where item_id = ?";

            try (PreparedStatement pstmt = conn.prepareStatement(sqlGetCurrent)) {
                pstmt.setInt(1, itemId);

                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next()) {
                        return rs.getInt("continuous_days") + 1;
                    } else {
                        throw new SQLException("CheckInItemDao.calculateContinuousDays: 未找到对应打卡项");
                    }
                }
            }
        } else {
            return 1;
        }
    }

    /**
     * 更新打卡项的连续打卡天数（内部辅助方法，需在事务中调用）
     *
     * @param conn             数据库连接（共享事务）
     * @param itemId           打卡项ID
     * @param continuousDays   新的连续打卡天数
     * @throws SQLException    当SQL执行失败或未找到对应打卡项时抛出
     */
    private void updateContinuousDays(Connection conn, int itemId, int continuousDays) throws SQLException {
        String sql = "update check_in_item set continuous_days = ? where item_id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, continuousDays);
            pstmt.setInt(2, itemId);

            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.updateContinuousDays: pstmt.executeUpdate() < 1");
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void updateItem(int itemId, String newName) throws SQLException {
        String sql = "update check_in_item set item_name = ? where item_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, newName);
            pstmt.setInt(2, itemId);

            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.updateItem: pstmt.executeUpdate() < 1");
            }
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public void deleteItem(int itemId) throws SQLException {
        String sql = "delete from check_in_item where item_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, itemId);
            if (pstmt.executeUpdate() < 1) {
                throw new SQLException("CheckInItemDao.deleteItem: pstmt.executeUpdate() < 1");
            }
        }
    }
}